# 526936c3fd9e5f74fe99ed17be74c6b3
import os

from flask import Flask, jsonify, request
from flask_jwt_extended import (
    JWTManager, create_access_token,
    jwt_required, get_jwt_identity
)
from werkzeug.security import check_password_hash, generate_password_hash
import pymysql
from pymysql import OperationalError, ProgrammingError
from pymysql.cursors import DictCursor
import datetime
from typing import Optional, Dict, Any
import logging


from werkzeug.utils import secure_filename  # 用于安全处理文件名


# 配置日志，便于调试和生产环境排查问题
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

app = Flask(__name__)

app = Flask(__name__, static_folder='static')  # 静态文件目录为 static

# 配置头像保存路径
AVATAR_UPLOAD_FOLDER = os.path.join(os.path.dirname(__file__), 'static/avatars')
os.makedirs(AVATAR_UPLOAD_FOLDER, exist_ok=True)
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif'}

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


# JWT 配置（生产环境建议通过环境变量注入密钥）
app.config['JWT_SECRET_KEY'] = 'your-super-secret-key-2024'
app.config['JWT_ACCESS_TOKEN_EXPIRES'] = datetime.timedelta(hours=1)
jwt = JWTManager(app)

# MySQL 配置（PyMySQL 推荐配置）
MYSQL_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'Li123456',
    'db': 'python_demo',
    'port': 3306,
    'charset': 'utf8mb4',
    'cursorclass': DictCursor,  # 全局默认返回字典格式
    'connect_timeout': 10,  # 连接超时（秒）
    'read_timeout': 10,     # 读取超时
    'write_timeout': 10,    # 写入超时
    'autocommit': False,    # 关闭自动提交，手动控制事务
}

# 数据库连接工具（PyMySQL 优化版）
def get_db_connection():
    """获取数据库连接，支持自动重连"""
    try:
        conn = pymysql.connect(** MYSQL_CONFIG)
        # 验证连接有效性（处理连接池超时问题）
        conn.ping(reconnect=True)  # PyMySQL 内置重连机制
        return conn
    except OperationalError as e:
        logger.error(f"数据库连接失败: {str(e)}")
        raise Exception(f"数据库连接错误: {str(e)}")


# 统一响应格式
def make_response(
    data: Optional[Dict[str, Any]] = None,
    code: int = 200,
    msg: str = "success"
) -> Dict[str, Any]:
    return jsonify({
        "data": data or {},
        "code": code,
        "msg": msg
    })


def dict_fetchone(cursor, row):
    """
    直接接收单条数据元组，转换为 字段名: 值 的字典
    :param cursor: 游标对象（用于获取字段名）
    :param row: 单条数据元组（如 (1, 'admin', 'pwd', ...)）
    :return: 字典（字段名: 值）或 None
    """
    # 获取所有字段名（从游标描述中提取）
    columns = [col[0] for col in cursor.description]
    # 确保 row 是有效元组，且长度与字段名一致
    if row and isinstance(row, tuple) and len(row) == len(columns):
        return dict(zip(columns, row))
    return None

def dict_fetchall(cursor):
    """将游标查询的多条结果转换为字典列表（可选，用于批量查询）"""
    columns = [col[0] for col in cursor.description]
    rows = cursor.fetchall()
    return [dict(zip(columns, row)) for row in rows]

# 用户注册接口
@app.route('/register', methods=['POST'])
def register():
    try:
        if not request.is_json:
            return make_response(code=400, msg="请求格式错误，需为 JSON")
        data = request.get_json()

        # 提取参数（PyMySQL 原生支持 Unicode，无需手动编码）
        username = data.get('username', '').strip()
        password = data.get('password', '').strip()
        gender = data.get('gender', 'unknown').strip()
        age = data.get('age')
        birth_date = data.get('birth_date') if data.get('birth_date') else None
        role = data.get('role', 'user').strip()

        # 参数验证
        if len(username) < 3 or len(username) > 50:
            return make_response(code=400, msg="用户名长度需在 3-50 字符之间")
        if len(password) < 6:
            return make_response(code=400, msg="密码长度不能少于 6 位")
        if age is not None and (not isinstance(age, int) or age < 0 or age > 150):
            return make_response(code=400, msg="年龄需为 0-150 之间的整数")

        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 检查用户名是否存在（使用参数化查询防注入）
                cursor.execute(
                    "SELECT username FROM person WHERE username = %s",
                    (username,)
                )
                if cursor.fetchone():
                    return make_response(code=400, msg=f"用户名 {username} 已被注册")

                # 密码加密并插入数据
                # hashed_password = generate_password_hash(password)
                hashed_password = password
                cursor.execute("""
                    INSERT INTO person (username, password, gender, age, birth_date, role)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """, (username, hashed_password, gender, age, birth_date, role))
                conn.commit()  # 手动提交事务

                return make_response(
                    data={"username": username, "role": role, "msg": "注册成功，请登录"},
                    code=200,
                    msg="用户注册成功"
                )
        except ProgrammingError as e:
            # SQL 语法错误或表结构问题
            if conn:
                conn.rollback()
            logger.error(f"注册 SQL 错误: {str(e)}")
            return make_response(code=500, msg="数据格式错误，请检查输入")
        except OperationalError as e:
            # 连接异常（如断连）
            if conn:
                conn.rollback()
            logger.error(f"注册数据库连接错误: {str(e)}")
            return make_response(code=500, msg="数据库连接异常，请重试")
        finally:
            if conn:
                conn.close()  # 确保连接关闭

    except Exception as e:
        logger.error(f"注册接口异常: {str(e)}")
        return make_response(code=500, msg="服务器内部错误")


# 用户登录接口
@app.route('/login', methods=['POST'])
def login():
    try:
        if not request.is_json:
            return make_response(code=400, msg="请求格式错误，需为 JSON")
        data = request.get_json()

        username = data.get('username', '').strip()
        password = data.get('password', '').strip()
        if not username or not password:
            return make_response(code=400, msg="用户名和密码不能为空")

        conn = None
        try:
            conn = get_db_connection()
            with conn.cursor() as cursor:
                # 查询用户信息（PyMySQL 游标默认返回字典，直接使用键名访问）
                cursor.execute(
                    "SELECT username, password, role FROM person WHERE username = %s",
                    (username,)
                )
                user = cursor.fetchone()

                if not user:
                    return make_response(code=401, msg="用户名不存在")
                # if not check_password_hash(user['password'], password):
                if user['password'] != password:
                    return make_response(code=401, msg="密码错误")

                # 生成 JWT Token
                access_token = create_access_token(
                    identity=user['username'],
                    additional_claims={"role": user['role']}
                )
                return make_response(
                    data={
                        "access_token": access_token,
                        "user": {"username": user['username'], "role": user['role']}
                    },
                    msg="登录成功"
                )
        except OperationalError as e:
            logger.error(f"登录数据库错误: {str(e)}")
            return make_response(code=500, msg="数据库访问失败，请重试")
        finally:
            if conn:
                conn.close()

    except Exception as e:
        logger.error(f"登录接口异常: {str(e)}")
        return make_response(code=500, msg="服务器内部错误")


# 获取用户信息接口
@app.route('/user/info', methods=['GET'])
@jwt_required()
def get_user_info():
    current_username = get_jwt_identity()
    conn = None
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT id, username, gender, age, birth_date, register_time, role 
                FROM person 
                WHERE username = %s
            """, (current_username,))
            user_info = cursor.fetchone()

            if not user_info:
                return make_response(code=404, msg="用户信息不存在")

            # 格式化时间（处理 None 情况，PyMySQL 日期返回 datetime 对象）
            if user_info.get('register_time'):
                user_info['register_time'] = user_info['register_time'].strftime('%Y-%m-%d %H:%M:%S')
            if user_info.get('birth_date'):
                user_info['birth_date'] = user_info['birth_date'].strftime('%Y-%m-%d')

            return make_response(data={"user_info": user_info})
    except OperationalError as e:
        logger.error(f"获取用户信息数据库错误: {str(e)}")
        return make_response(code=500, msg="获取用户信息失败")
    finally:
        if conn:
            conn.close()

# 新增：查询person表所有数据接口（带分页和权限控制）
@app.route('/persons', methods=['GET'])
@jwt_required()
def get_all_persons():
    # 验证用户角色（仅管理员可访问）
    current_user = get_jwt_identity()
    conn = None
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 1. 先查询当前用户角色，验证权限
            cursor.execute(
                "SELECT role FROM person WHERE username = %s",
                (current_user,)
            )
            user = cursor.fetchone()
            # if not user or user['role'] != 'admin':  # 假设管理员角色为 'admin'
            #     return make_response(code=403, msg="权限不足，仅管理员可访问")

            # 2. 处理分页参数（默认第1页，每页20条）
            page = request.args.get('page', 1, type=int)
            per_page = request.args.get('per_page', 20, type=int)
            if page < 1 or per_page < 1 or per_page > 100:  # 限制每页最大100条
                return make_response(code=400, msg="分页参数错误，page≥1且1≤per_page≤100")

            offset = (page - 1) * per_page  # 计算偏移量

            # 3. 查询总条数（用于分页导航）
            cursor.execute("SELECT COUNT(*) AS total FROM person")
            total = cursor.fetchone()['total']

            # 4. 查询当前页数据
            cursor.execute("""
                SELECT id, username, gender, age, birth_date, register_time, role ,avatar
                FROM person 
                ORDER BY register_time DESC  # 按注册时间倒序（最新的在前）
                LIMIT %s OFFSET %s
            """, (per_page, offset))
            persons = cursor.fetchall()

            # 5. 格式化时间字段
            for person in persons:
                if person.get('register_time'):
                    person['register_time'] = person['register_time'].strftime('%Y-%m-%d %H:%M:%S')
                if person.get('birth_date'):
                    person['birth_date'] = person['birth_date'].strftime('%Y-%m-%d')

            # 6. 构造分页响应数据
            return make_response(data={
                "total": total,  # 总条数
                "page": page,    # 当前页
                "per_page": per_page,  # 每页条数
                "total_pages": (total + per_page - 1) // per_page,  # 总页数
                "items": persons  # 当前页数据
            })

    except OperationalError as e:
        logger.error(f"查询person表数据库错误: {str(e)}")
        return make_response(code=500, msg="查询数据失败")
    finally:
        if conn:
            conn.close()


# 补充：获取单个用户详情接口（可选，按ID查询）
@app.route('/persons/<int:user_id>', methods=['GET'])
@jwt_required()
def get_person_by_id(user_id):
    current_user = get_jwt_identity()
    conn = None
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 1. 查询目标用户信息
            cursor.execute("""
                SELECT id, username, gender, age, birth_date, register_time, role 
                FROM person 
                WHERE id = %s
            """, (user_id,))
            person = cursor.fetchone()

            if not person:
                return make_response(code=404, msg="用户不存在")

            # 2. 权限控制：只能查看自己的信息或管理员可查看所有
            cursor.execute("SELECT username, role FROM person WHERE username = %s", (current_user,))
            current_user_info = cursor.fetchone()
            if current_user_info['role'] != 'admin' and current_user_info['username'] != person['username']:
                return make_response(code=403, msg="权限不足，无法查看该用户信息")

            # 3. 格式化时间
            if person.get('register_time'):
                person['register_time'] = person['register_time'].strftime('%Y-%m-%d %H:%M:%S')
            if person.get('birth_date'):
                person['birth_date'] = person['birth_date'].strftime('%Y-%m-%d')

            return make_response(data={"person": person})

    except OperationalError as e:
        logger.error(f"查询单个用户数据库错误: {str(e)}")
        return make_response(code=500, msg="查询用户信息失败")
    finally:
        if conn:
            conn.close()

# 编辑用户接口（支持头像上传 + 字典游标直接用）
@app.route('/user/edit', methods=['post'])
@jwt_required()
def edit_user():
    current_username = get_jwt_identity()
    try:
        # 1. 获取表单数据（multipart/form-data 格式）
        user_id = request.form.get('id')
        if not user_id or not user_id.isdigit():
            return make_response(code=400, msg="缺少或无效的 user_id（必须为整数）")
        user_id = int(user_id)

        # 2. 提取基础字段
        username = request.form.get('username', '').strip()
        gender = request.form.get('gender', '').strip()
        age = request.form.get('age')
        birth_date = request.form.get('birth_date')
        role = request.form.get('role', '').strip()

        # 3. 处理头像文件
        avatar_path = None
        if 'avatar' in request.files:
            avatar_file = request.files['avatar']
            if avatar_file.filename != '' and allowed_file(avatar_file.filename):
                # 安全生成唯一文件名
                filename = secure_filename(avatar_file.filename)
                timestamp = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
                name, ext = os.path.splitext(filename)
                unique_filename = f"avatar_{user_id}_{timestamp}{ext}"
                # 保存文件
                save_path = os.path.join(AVATAR_UPLOAD_FOLDER, unique_filename)
                avatar_file.save(save_path)
                # 存储相对路径
                avatar_path = f"avatars/{unique_filename}"

        # 4. 数据验证
        if age:
            try:
                age = int(age)
                if age < 0 or age > 150:
                    return make_response(code=400, msg="年龄需为 0-150 之间的整数")
            except ValueError:
                return make_response(code=400, msg="年龄必须为整数")

        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:  # 字典游标，返回字典格式
                # 5. 权限校验：普通用户只能改自己，管理员可改所有
                cursor.execute("SELECT role FROM person WHERE username = %s", (current_username,))
                current_user = cursor.fetchone()  # 直接获取字典，无需转换
                if not current_user:
                    return make_response(code=404, msg="当前登录用户不存在")

                # 普通用户权限校验
                if current_user['role'] != 'admin':
                    cursor.execute("SELECT id FROM person WHERE id = %s AND username = %s", (user_id, current_username))
                    target_user = cursor.fetchone()
                    if not target_user:
                        return make_response(code=403, msg="无权修改该用户信息")

                # 6. 动态构建更新 SQL
                update_fields = []
                params = []
                if username:
                    update_fields.append("username = %s")
                    params.append(username)
                if role and current_user['role'] == 'admin':  # 管理员可改角色
                    update_fields.append("role = %s")
                    params.append(role)
                if gender:
                    update_fields.append("gender = %s")
                    params.append(gender)
                if age is not None:
                    update_fields.append("age = %s")
                    params.append(age)
                if birth_date:
                    update_fields.append("birth_date = %s")
                    params.append(birth_date)
                if avatar_path:
                    update_fields.append("avatar = %s")
                    params.append(avatar_path)

                if not update_fields:
                    return make_response(code=400, msg="未提供需修改的字段")

                # 7. 执行更新
                update_sql = f"UPDATE person SET {', '.join(update_fields)} WHERE id = %s"
                params.append(user_id)
                cursor.execute(update_sql, params)
                conn.commit()

                # 8. 返回更新后的用户信息
                cursor.execute("""
                    SELECT id, username, gender, age, birth_date, register_time, role, avatar 
                    FROM person WHERE id = %s
                """, (user_id,))
                updated_user = cursor.fetchone()  # 直接获取字典
                if not updated_user:
                    return make_response(code=404, msg="更新后用户信息获取失败")

                # 9. 格式化时间字段
                if isinstance(updated_user.get('register_time'), datetime.datetime):
                    updated_user['register_time'] = updated_user['register_time'].strftime('%Y-%m-%d %H:%M:%S')
                if isinstance(updated_user.get('birth_date'), datetime.date):
                    updated_user['birth_date'] = updated_user['birth_date'].strftime('%Y-%m-%d')

                # 10. 拼接头像完整 URL
                if updated_user.get('avatar'):
                    updated_user['avatar_url'] = f"http://127.0.0.1:5000/static/{updated_user['avatar']}"

                return make_response(
                    data={"user_info": updated_user},
                    code=200,
                    msg="用户信息（含头像）修改成功"
                )
        finally:
            conn.close()


    except OperationalError as e:
        return make_response(code=500, msg=f"数据库错误：{str(e)}")
    except Exception as e:
        return make_response(code=500, msg=f"服务器错误：{str(e)}")


# 删除用户接口（路径参数id + 管理员权限 + 返回删除信息）
@app.route('/user/delete/<int:user_id>', methods=['DELETE'])  # 路径参数：user_id（整数）
@jwt_required()
def delete_user(user_id):
    current_username = get_jwt_identity()  # 获取当前登录用户名
    print(current_username,' current_username')
    try:
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 1. 查询当前登录用户的角色（判断是否为管理员）
                cursor.execute("SELECT * FROM person WHERE username = %s", (current_username,))

                # 获取所有查询结果（返回列表，每个元素是一行数据的元组）
                result = cursor.fetchall()

                # 打印结果
                if result:  # 判断是否有查询结果
                    print("查询到的数据：")
                    for row in result:
                        print(row)  # 打印整行数据
                        # 如果需要单独打印每个字段（例如：id、username、role等），可以按索引或字段名获取
                        # 示例：打印用户名和角色
                        # print(f"用户名：{row[1]}, 角色：{row[7]}")  # 假设索引1是username，索引7是role
                else:
                    print("未查询到该用户")

                print(result[0], 'result[0] current_user1')
                current_user = result[0] # dict_fetchone(cursor, result[0])
                print(current_user, current_username,' current_user1')
                if not current_user:
                    return make_response(code=404, msg="当前登录用户不存在")
                print(current_user, ' current_user')
                # 2. 检查是否为管理员（role = 'admin'）
                if current_user['role'] != 'admin':
                    return make_response(code=403, msg="权限不足：仅管理员可删除用户")

                # 3. 查询被删除的用户是否存在（通过user_id）
                cursor.execute("""
                    SELECT id, username, gender, age, birth_date, register_time, role 
                    FROM person WHERE id = %s
                """, (user_id,))
                delete_user_result = cursor.fetchall()
                deleted_user = delete_user_result[0] #dict_fetchone(cursor, result[0])
                if not deleted_user:
                    return make_response(code=404, msg=f"用户id {user_id} 不存在")

                # 4. 执行删除操作
                cursor.execute("DELETE FROM person WHERE id = %s", (user_id,))
                conn.commit()

                # 5. 格式化被删除用户的时间字段（返回给前端）
                if isinstance(deleted_user.get('register_time'), datetime.datetime):
                    deleted_user['register_time'] = deleted_user['register_time'].strftime('%Y-%m-%d %H:%M:%S')
                if isinstance(deleted_user.get('birth_date'), datetime.date):
                    deleted_user['birth_date'] = deleted_user['birth_date'].strftime('%Y-%m-%d')

                # 6. 返回被删除的用户信息
                return make_response(
                    data={"deleted_user": deleted_user},
                    code=200,
                    msg=f"用户id {user_id}（{deleted_user['username']}）已成功删除"
                )
        finally:
            conn.close()

    except OperationalError as e:
        return make_response(code=500, msg=f"数据库错误：{str(e)}")
    except Exception as e:
        return make_response(code=500, msg=f"服务器错误：{str(e)}")

# JWT 错误处理
@jwt.expired_token_loader
def expired_token_callback(jwt_header, jwt_payload):
    return make_response(code=401, msg="Token 已过期，请重新登录")

@jwt.invalid_token_loader
def invalid_token_callback(error):
    return make_response(code=401, msg="无效的 Token，请检查")

@jwt.unauthorized_loader
def missing_token_callback(error):
    return make_response(code=401, msg="未提供 Token，请先登录")


if __name__ == '__main__':
    # 生产环境建议使用 Gunicorn 等 WSGI 服务器，关闭 debug
    app.run(debug=True, host='0.0.0.0', port=5000)